package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

/**
 * 景区店铺 收藏信息—SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_XwScecollect {

    /**
     *  获取农家乐店铺 评论信息
     */
    public JSONObject getSceinccollectlist(Integer pageindex, Integer pagesize, String starttime, String endtime,
                                      String sortfild, String sortway,String searchval,String searchkey,
                                         String scenic_encryptId,String user_encryptId,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  a.scenic_collect_id         AS collect_id,\n" +
                "  a.business_encryptId          AS business_encryptId,\n" +
                "  a.scenic_encryptId          AS scenic_encryptId,\n" +
                "  a.user_encryptId          AS user_encryptId,\n" +
                "\n" +
                "  a.scceincTypeName            AS scceincTypeName,\n" +
                "  b.business_type          AS businessTypeid,\n" +
                "  b.business_CompanyName   AS CompanyName,\n" +
                "  a.scenic_collect_username   AS username,\n" +
                "  a.scenic_collect_createtime AS createtime\n" +
                "\n" +
                "FROM xw_sceinc_collectinfo a\n" +
                "  LEFT JOIN xw_business_info b ON a.business_encryptId = b.business_encryptId\n" +
                "                                  AND a.scenic_encryptId = b.sec_encryptId\n" +
                "  LEFT JOIN xw_userinfo c ON a.user_encryptId = c.user_encryptId ";

        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        if(!starttime.equals("")){
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and a.scenic_collect_createtime >= '"+starttime+"' and a.scenic_collect_createtime <= '"+ endtime + "' ");
        }
        //店铺
        if(!scenic_encryptId.equals("")){
            sb.append(" and a.scenic_encryptId in('" + scenic_encryptId + "') ");
        }

        //用户
        if(!user_encryptId.equals("")){
            sb.append(" and a.user_encryptId in('" + user_encryptId + "') ");
        }

        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by a." + sortfild + " " + sortway + " ");
        }

        try {

            sql = sql + " where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = " SELECT\n" +
                    "  count(*)\n" +
                    "\n" +
                    "FROM xw_sceinc_collectinfo a\n" +
                    "  LEFT JOIN xw_business_info b ON a.business_encryptId = b.business_encryptId\n" +
                    "                                  AND a.scenic_encryptId = b.sec_encryptId\n" +
                    "  LEFT JOIN xw_userinfo c ON a.user_encryptId = c.user_encryptId " +
                    " WHERE "+ sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("collect_id", (rs.getString("collect_id") == null ? "" : rs.getString("collect_id")));
                object.put("business_encryptId", (rs.getString("business_encryptId") == null ? "" : rs.getString("business_encryptId")));
                object.put("scenic_encryptId", (rs.getString("scenic_encryptId") == null ? "" : rs.getString("scenic_encryptId")));
                object.put("user_encryptId", (rs.getString("user_encryptId") == null ? "" : rs.getString("user_encryptId")));
                object.put("scceincTypeName", (rs.getString("scceincTypeName") == null ? "" : rs.getString("scceincTypeName")));
                object.put("businessTypeid", (rs.getString("businessTypeid") == null ? "" : rs.getString("businessTypeid")));
                object.put("CompanyName", (rs.getString("CompanyName") == null ? "" : rs.getString("CompanyName")));
                object.put("username", (rs.getString("username") == null ? "" : rs.getString("username")));
                object.put("createtime", (rs.getString("createtime") == null ? "" : DateUtil.DateToString(rs.getDate("createtime"))));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);

        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }

}
